use super::firm_type;
use super::get_pool;
use crate::error::AppErr;
use crate::error::UniqueMap;
use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_proj (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL, 
        description TEXT NOT NULL,
        version INTEGER,
        UNIQUE (name, version)
    )
"#;

pub async fn init() -> Result<(), AppErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct TbProj {
    id: i64,
    name: String,
    description: String,
    version: u32,
}

pub async fn delete(id: i64) -> Result<(), AppErr> {
    let mut tx = get_pool().begin().await?;

    sqlx::query("DELETE FROM tb_proj WHERE id = ?")
        .bind(id)
        .execute(&mut *tx)
        .await?;

    firm_type::delete_with_proj(id, &mut tx).await?;

    tx.commit().await?;

    Ok(())
}

pub async fn query() -> Result<Box<[TbProj]>, AppErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, description, version FROM tb_proj ORDER BY id DESC
    "#,
    )
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let item = TbProj {
            id: row.get(0),
            name: row.get(1),
            description: row.get(2),
            version: row.get(3),
        };
        vec.push(item);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn create(name: &str, description: &str, version: u32) -> Result<i64, AppErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_proj (name, description, version) VALUES (?, ?, ?)
    "#,
    )
    .bind(name)
    .bind(description)
    .bind(version)
    .execute(get_pool())
    .await
    .map_unique("名称/版本号重复")?;

    Ok(ret.last_insert_rowid())
}
